package com.secneo.appshare.database;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class AppShareDatabaseHelper extends SQLiteOpenHelper {
	ArrayList<AppFile> dsaAl = new ArrayList<AppFile>();
	ArrayList<AppMdSha> appMdShaAl = new ArrayList<AppMdSha>();
	private static final String DATABASE_NAME = "AppShare.db";
	private static final int DATABASE_VERSION = 3;// not used
	private static final String APPFILE_TABLE_NAME = "AppFile";
	private static final String APPMDSHA_TABLE_NAME = "AppMdSha";
	private Context ctxx;

	public AppShareDatabaseHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		ctxx = context;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE IF NOT EXISTS AppFile" + "("
				+ " PackageName TEXT," + " Name TEXT," + " IconName TEXT,"
				+ " Cateogry TEXT," + " Comment TEXT," + " UpdateServer INT,"
				+ " IsFavorite INT," + " IsAddictive INT," + " ViaUs INT,"
				+ " Uninst INT," + " RunCount int," + " Hidden INT,"
				+ " IsViaRecom INT" + ");");
		
		db.execSQL("CREATE TABLE IF NOT EXISTS "+APPMDSHA_TABLE_NAME + "("
				+ "packageName TEXT, " + " version TEXT, "
				+ " appMd5 TEXT, " + " appSha TEXT, "
				+ " memo TEXT, " + " status INT, "+  " updateServer INT"+ ");");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

		db.execSQL("DROP TABLE IF EXISTS " + APPFILE_TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + APPMDSHA_TABLE_NAME);
		onCreate(db);
	}

	// 添加一条记录
	public long insertAppFile(AppFile appFile) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues cv = new ContentValues();
		cv.put("PackageName", appFile.getPackageName());
		cv.put("Name", appFile.getName());
		cv.put("IconName", appFile.getIconName());

		cv.put("Cateogry", appFile.getCateogry());
		cv.put("Comment", appFile.getComment());
		cv.put("UpdateServer", appFile.getUpdateServer());
		cv.put("IsFavorite", appFile.getIsFavorite());
		cv.put("IsAddictive", appFile.getIsAddictive());
		cv.put("ViaUs", appFile.getViaUs());
		cv.put("Uninst", appFile.getUninst());
		cv.put("RunCount", appFile.getRunCount());
		cv.put("Hidden", appFile.getHidden());
		cv.put("IsViaRecom", appFile.getIsViaRecom());
		long result = db.insert(APPFILE_TABLE_NAME, null, cv);
		db.close();
		return result;
	}
	
	// 添加一条记录
	public long insertAppMd5Sha(AppMdSha appMdSha) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues cv = new ContentValues();
		cv.put("packageName", appMdSha.getPackageName());
		cv.put("version", appMdSha.getVersion());
		cv.put("appMd5", appMdSha.getAppMd5());
		cv.put("appSha", appMdSha.getAppSha());
		cv.put("memo", appMdSha.getMemo());
		cv.put("status", appMdSha.getStatus());
		cv.put("updateServer",appMdSha.getUpdateServer());
		long result = db.insert(APPMDSHA_TABLE_NAME, null, cv);
		db.close();
		return result;
	}

	/*
	 * 删除包
	 */
	public void deleteAppFile(String packageName) {
		SQLiteDatabase db = this.getWritableDatabase();
		String sql = "delete from " + APPFILE_TABLE_NAME
				+ " where PackageName=" + packageName + ";";
		try {
			db.execSQL(sql);
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			db.close();
		}
	}

	/*
	 * 清空APPFILE_TABLE_NAME 表
	 */
	public void cleanAppFileTable()
	  {
		SQLiteDatabase db=this.getWritableDatabase(); 
		String sql="delete from "+APPFILE_TABLE_NAME+";"; 
		String sql1="update sqlite_sequence set seq=0 where name="+APPFILE_TABLE_NAME;
		try{ 
	    	db.execSQL(sql); 
	    	db.execSQL(sql1); 
	    	}catch(Exception ex){ 
	    		ex.printStackTrace();
	    	} 
	    	finally
	    	{
	    		 db.close();
	    	}
	  }
	/*
	 * 更新用户程序信息
	 */

	public void updateAppFile(AppFile appFile) throws Exception {
		if (appFile == null)
			return;

		SQLiteDatabase db = this.getWritableDatabase();
		db.beginTransaction();
		try {
			String where = " PackageName=  ? ";
			String[] whereValue = { appFile.getPackageName() };
			ContentValues values = toContentValues(appFile);

			long rowId = db.update(APPFILE_TABLE_NAME, values, where,
					whereValue);
			Log.d("rowId***********:", "" + rowId);
			db.setTransactionSuccessful();

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			db.endTransaction();
			db.close();

		}

	}

	private ContentValues toContentValues(AppFile appFile) {
		ContentValues cv = new ContentValues();
		cv.put("PackageName", appFile.getPackageName());
		cv.put("UpdateServer", appFile.getUpdateServer());
		cv.put("IsFavorite", appFile.getIsFavorite());
		cv.put("IsAddictive", appFile.getIsAddictive());
		cv.put("RunCount", appFile.getRunCount());
		cv.put("Uninst", appFile.getUninst());
		cv.put("Hidden", appFile.getHidden());
		cv.put("IsViaRecom", appFile.getIsViaRecom());

		return cv;
	}
	/*
	 * 更加密程序信息
	 */

	public void updateAppMdSha(AppMdSha appMdSha) throws Exception {
		if (appMdSha == null)
			return;

		SQLiteDatabase db = this.getWritableDatabase();
		db.beginTransaction();
		try {
			String where = " packageName=  ? ";
			String[] whereValue = { appMdSha.getPackageName() };
			ContentValues values = getContentValues(appMdSha);

			long rowId = db.update(APPMDSHA_TABLE_NAME, values, where,
					whereValue);
			Log.d("rowId***********:", "" + rowId);
			db.setTransactionSuccessful();

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			db.endTransaction();
			db.close();

		}

	}

	private ContentValues getContentValues(AppMdSha appMdSha) {
		ContentValues cv = new ContentValues();
		cv.put("packageName", appMdSha.getPackageName());
		cv.put("version", appMdSha.getVersion());
		cv.put("appMd5", appMdSha.getAppMd5());
		cv.put("appSha", appMdSha.getAppSha());
		cv.put("memo", appMdSha.getMemo());
		cv.put("status", appMdSha.getStatus());
		cv.put("updateServer",appMdSha.getUpdateServer());
		return cv;
	}
	/*
	 * 获取所有的未上传加密程序
	 * BY updateServer
	 */
	public ArrayList<AppMdSha> getAppMdSha(AppShareDatabaseHelper mOpenHelper) {
		SQLiteDatabase db = mOpenHelper.getReadableDatabase();
		Cursor c = db.query(APPMDSHA_TABLE_NAME, null, " updateServer="+0,
				null, null, null, null);
		if (c.moveToFirst()) {
			do {
				AppMdSha appMdSha = new AppMdSha();
				appMdSha.setPackageName(c.getString(0));
				appMdSha.setVersion(c.getString(1));
				appMdSha.setAppMd5(c.getString(2));
				appMdSha.setAppSha(c.getString(3));
				appMdSha.setMemo(c.getString(4));
				appMdSha.setStatus(c.getInt(5));
				appMdShaAl.add(appMdSha);
			} while (c.moveToNext());
		}
		c.close();
		db.close();
		return appMdShaAl;
	}
	/*
	 * 获取所有的未上传加密程序
	 */
	public ArrayList<AppMdSha> getAppMdShas(AppShareDatabaseHelper mOpenHelper) {
		SQLiteDatabase db = mOpenHelper.getReadableDatabase();
		Cursor c = db.query(APPMDSHA_TABLE_NAME, null, null,
				null, null, null, null);
		if (c.moveToFirst()) {
			do {
				AppMdSha appMdSha = new AppMdSha();
				appMdSha.setPackageName(c.getString(0));
				appMdSha.setVersion(c.getString(1));
				appMdSha.setAppMd5(c.getString(2));
				appMdSha.setAppSha(c.getString(3));
				appMdSha.setMemo(c.getString(4));
				appMdSha.setStatus(c.getInt(5));
				appMdShaAl.add(appMdSha);
			} while (c.moveToNext());
		}
		c.close();
		db.close();
		return appMdShaAl;
	}
	/*
	 * 获取所有的未上传默认程序
	 */
	public ArrayList<AppFile> getAppFile(AppShareDatabaseHelper mOpenHelper) {
		SQLiteDatabase db = mOpenHelper.getReadableDatabase();
		dsaAl = new ArrayList<AppFile>();
		Cursor c = db.query(APPFILE_TABLE_NAME, null, " UpdateServer="+0,
				null, null, null, null);
		if (c.moveToFirst()) {
			do {
				AppFile appFile = new AppFile();
				appFile.setPackageName(c.getString(0));
				appFile.setName(c.getString(1));
				appFile.setIsFavorite(c.getInt(6));
				appFile.setRunCount(c.getInt(10));
				appFile.setHidden(c.getInt(11));
				dsaAl.add(appFile);
			} while (c.moveToNext());
		}
		c.close();
		db.close();
		return dsaAl;
	}
	/*
	 * 获取所有的用户已卸载程序
	 */
	public ArrayList<AppFile> getAppFileByUninst(AppShareDatabaseHelper mOpenHelper) {
		SQLiteDatabase db = mOpenHelper.getReadableDatabase();
		dsaAl = new ArrayList<AppFile>();
		Cursor c = db.query(APPFILE_TABLE_NAME, null, " Uninst="+1,
				null, null, null, null);
		if (c.moveToFirst()) {
			do {
				AppFile appFile = new AppFile();
				appFile.setPackageName(c.getString(0));
				appFile.setName(c.getString(1));
				appFile.setIsFavorite(c.getInt(6));
				appFile.setUninst(c.getInt(9));
				appFile.setRunCount(c.getInt(10));
				appFile.setHidden(c.getInt(11));
				dsaAl.add(appFile);
			} while (c.moveToNext());
		}
		c.close();
		db.close();
		return dsaAl;
	}
	/*
	 * 获取所有的默认程序
	 */
	public ArrayList<AppFile> getAppFiles(AppShareDatabaseHelper mOpenHelper) {
		SQLiteDatabase db = mOpenHelper.getReadableDatabase();
		dsaAl = new ArrayList<AppFile>();
		Cursor c = db.query(APPFILE_TABLE_NAME, null, null,
				null, null, null, null);
		if (c.moveToFirst()) {
			do {
				AppFile appFile = new AppFile();
				appFile.setPackageName(c.getString(0));
				appFile.setName(c.getString(1));
				appFile.setIsFavorite(c.getInt(6));
				appFile.setRunCount(c.getInt(10));
				appFile.setHidden(c.getInt(11));
				dsaAl.add(appFile);
			} while (c.moveToNext());
		}
		c.close();
		db.close();
		return dsaAl;
	}
	
	
	/*
	 * 获取所有的默认程序
	 */
	public AppFile getAppFileByPackageName(AppShareDatabaseHelper mOpenHelper,String packageName) {
		SQLiteDatabase db = mOpenHelper.getReadableDatabase();
		Cursor c = db.query(APPFILE_TABLE_NAME, null, " PackageName='"+packageName+"'",
				null, null, null, null);
		
		AppFile appFile = null;
		if (c.moveToFirst()) {
			do {
				appFile = new AppFile();
				appFile.setPackageName(c.getString(0));
				appFile.setName(c.getString(1));
				appFile.setIsFavorite(c.getInt(6));
				appFile.setRunCount(c.getInt(10));
				appFile.setHidden(c.getInt(11));

				/*
				 * + " PackageName TEXT," + " Name TEXT," + " IconName TEXT," +
				 * " Cateogry TEXT," + " Comment TEXT," + " UpdateServer INT," +
				 * " IsFavorite boolean," + " IsAddictive boolean," +
				 * " ViaUs boolean," + " Uninst boolean," + " RunCount int," +
				 * " Hidden boolean," + " IsViaRecom boolean"
				 */
				
			} while (c.moveToNext());
		}
		c.close();
		db.close();
		return appFile;
	}

}
